//  -------------------- iSharePoint -------------------- //
// 
//  Company:	IT Complex, LLC
//  Project:	Smart.Common
//  File name:	SqlExts.cs
//  Developer:	Solomatov Igor
//  Created:	15.04.2012
// 
//  -------------------- iSharePoint -------------------- //

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;

namespace Smart
{
	[Localizable(false)]
	[DebuggerStepThrough]
	public static class SqlExts
	{
		/// <summary>
		/// Checks and Opens connection if it was not opened
		/// </summary>
		public static SqlConnection CheckOpen(this SqlConnection conn)
		{
			if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
				conn.Open();

			return conn;
		}

		/// <summary>
		/// Checks and Opens connection if it was not opened
		/// </summary>
		public static bool InNormalOpenState(this SqlConnection conn)
		{
			return (conn.State == ConnectionState.Open);
		}

		public static string AddParameter(this List<SqlParameter> parameters, string prefix, object value)
		{
			if (value == null)
				return "NULL";

			// Try to remove parameters overhead with some datatypes

			if (value is int || value is long || value is float || value is double /*|| value is DateTime*/ || value is bool || value is Guid)
				return value.IncapsulateSql();

			var svalue = value as string;

			if (svalue != null && svalue.Length < 256)
				return svalue.IncapsulateSql();

			var name = prefix + parameters.Count;

			parameters.Add(new SqlParameter(name, value ?? DBNull.Value));

			return "@" + name;
		}

		public static void GetInsertParameters(this Dictionary<string, object> row, string prefix, List<SqlParameter> parameters, out string columns, out string values)
		{
			var cols = new StringBuilder();
			var vals = new StringBuilder();

			foreach (var value in row)
			{
				cols.Append(value.Key).Append(",");
				vals.Append(parameters.AddParameter(prefix, value.Value)).Append(",");
			}

			columns = cols.ToString(0, cols.Length - 1);
			values = vals.ToString(0, vals.Length - 1);
		}

		public static void GetUpdateParameters(this Dictionary<string, object> row, string prefix, List<SqlParameter> parameters, out string columnValues)
		{
			var cv = new StringBuilder();

			foreach (var value in row)
			{
				cv.Append(value.Key).Append("=").Append(parameters.AddParameter(prefix, value.Value)).Append(",");
			}

			columnValues = cv.ToString(0, cv.Length - 1);
		}

		/// <summary>
		/// Patch null values
		/// </summary>
		public static object PatchFromSql(this object value)
		{
			return value.IsNull() ? null : value;
		}

		public static string EncodeSql(this string data)
		{
#warning PERFORMANCE BUG: REWRITE WITH FASTER StringBuilder AND WITH ONE ITERATION!

			if (string.IsNullOrEmpty(data))
				return string.Empty;

			return
				data
					.Replace("'", "''")
					.Replace("\x0D\x0A", "'+CHAR(13)+CHAR(10)+'")
					.Replace("\x0D", "'+CHAR(13)+'")
					.Replace("\x0A", "'+CHAR(10)+'");
		}

		public static string IncapsulateSql(this int data)
		{
			return data.ToString(H.En);
		}

		public static string IncapsulateSql(this long data)
		{
			return data.ToString(H.En);
		}

		public static string IncapsulateSql(this double data)
		{
			return data.ToString(H.En);
		}

		public static string IncapsulateSql(this float data)
		{
			return data.ToString(H.En);
		}

		public static string IncapsulateSql(this decimal data)
		{
			return data.ToString(H.En);
		}

		public static string IncapsulateSql(this Guid data)
		{
			return data == Guid.Empty ? "NULL" : String.Concat("'", data.ToString("D"), "'");
		}

		public static string IncapsulateSql(this string data)
		{
			return String.Concat("N'", EncodeSql(data), "'");
		}

		public static string IncapsulateSql(this DateTime value, bool includeTimeValue)
		{
			return
				includeTimeValue
					? String.Concat("CONVERT(datetime, '", value.ToString("yyyy-MM-dd HH:mm:ss"), "', 120)")
					: String.Concat("CONVERT(datetime, '", value.ToString("yyyy-MM-dd"), "', 120)");
		}

		public static string IncapsulateSql(this object value)
		{
			if (value == null)
				return null;

			var svalue = value as string;
			if (svalue != null)
				return IncapsulateSql(svalue);
			if (value is DateTime)
				return IncapsulateSql((DateTime)value, true);
			if (value is int)
				return IncapsulateSql((int)value);
			if (value is long)
				return IncapsulateSql((long)value);
			if (value is double)
				return IncapsulateSql((double)value);
			if (value is float)
				return IncapsulateSql((float)value);
			if (value is decimal)
				return IncapsulateSql((decimal)value);
			if (value is Guid)
				return IncapsulateSql((Guid)value);
			if (value is char)
				return String.Concat("N'", value, "'");
			if (value is bool)
				return ((bool)value) ? "1" : "0";

			return IncapsulateSql(value.ToString());
		}

		public const string DefaultInstanceName = @"MSSQLServer";
		public const string DefaultServiceName = @"MSSQLSERVER";
		public const string DefaultServiceNamePrefix = @"MSSQL$";

		public static bool IsDefaultInstanceName(string instanceName)
		{
			return
				String.IsNullOrEmpty(instanceName) ||
				String.Equals(instanceName, DefaultInstanceName, StringComparison.OrdinalIgnoreCase);
		}

		public static string GetInstanceName(string instanceName)
		{
			return
				IsDefaultInstanceName(instanceName)
					? DefaultInstanceName
					: instanceName;
		}

		public static string GetServerName(string serverName, string instanceName)
		{
			return
				IsDefaultInstanceName(instanceName)
					? serverName
					: string.Concat(serverName, @"\", instanceName);
		}

		public static string GetServiceName(string instanceName)
		{
			return
				IsDefaultInstanceName(instanceName)
					? DefaultServiceName
					: String.Concat(DefaultServiceNamePrefix, instanceName);
		}

		public static void SplitServerName(string fullServerName, out string serverName, out string instanceName)
		{
			serverName = null;
			instanceName = null;

			var dsn = fullServerName.Split('\\');

			if (dsn.Length > 0)
				serverName = dsn[0];

			if (dsn.Length > 1)
				instanceName = dsn[1];
		}
	}
}